package com.sd.repay.sms;

import com.sd.repay.dao.BaseDao;
import com.sd.repay.pojo.SmsInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 短信DB操作类，所有短信操作都在这里管理
 */
@Service
public class SmsDb {
    private static final Logger log = LoggerFactory.getLogger(SmsDb.class);
    @Resource
    private BaseDao dao;

    /**
     * @param businessCode
     * @return
     */
    public Map<String, Object> getSmsRouteByBusinessCode(String businessCode) {
        String sql = "select * from t_sms_route s where s.business_code=?";
        return dao.findFirst(sql, businessCode);
    }

    /**
     * 统计未验证记录数
     *
     * @param moblie       手机号
     * @param sms_business 业务代码
     * @return
     */
    public long getNoCheckCodeCount(String moblie, String sms_business) {
        int count = 0;
        String sql = "select count(id) as count from t_sms_info where sms_type='1' and vali_status='1' and mobiles=? and sms_business=?";
        Map<String, Object> map = dao.findFirst(sql, new Object[]{moblie, sms_business});
        if (map != null && !map.isEmpty()) {
            count = Integer.valueOf(map.get("count").toString());
        }
        return count;
    }

    /**
     * 重置验证码为废弃
     *
     * @param moblie       手机号
     * @param sms_business 业务类型
     * @return
     */
    public int flushSmsCheckCode(String moblie, String sms_business) {
        int count = 0;
        String sql = "update t_sms_info set vali_status='3' where mobiles=? and vali_status='1' and sms_type='1' and sms_business=?";
        try {
            count = dao.execute(sql, new Object[]{moblie, sms_business});
        } catch (Exception e) {
            e.printStackTrace();
            return count;
        }
        return count;
    }

    /**
     * 获取短信风控名单
     *
     * @param checkValue 风控值
     * @return
     */
    public Map<String, Object> getSmsRisk(String checkValue) {
        String sql = "select * from t_sms_risk where risk_value = ? and risk_status='1'";
        return dao.findFirst(sql, checkValue);
    }


    /**
     * 修改验证校验状态
     *
     * @param info
     * @param minute
     * @return
     */
    public int valiCode(SmsInfo info, int minute) {
        int count = 0;
        String sql = "update t_sms_info s set s.vali_status='2' where s.mobiles=? and s.sms_business=? and s.vali_code=? and timestampdiff(MINUTE,s.create_time,NOW())<? and s.vali_status='1'";
        try {
            count = dao.execute(sql, new Object[]{info.getMobiles(), info.getSms_business(), info.getVali_code(), minute});
        } catch (Exception e) {
            log.error("手机号[{}]验证码[{}]业务代码[{}]验证码校验失败:{}", info.getMobiles(), info.getVali_code(), info.getSms_business(), e);
            return count;
        }
        return count;
    }


    /**
     * 根据订单号查询短信发送记录
     *
     * @param orderNo 订单号
     * @return smsInfo
     */
    public Map<String, Object> querySmsInfoByOrderNo(String orderNo) {
        String sql = "select * from t_sms_info where req_id=?";
        return dao.findFirst(sql, orderNo);
    }

    /**
     * 更新短信状态
     *
     * @param smsInfo 短信实体对象
     * @return 受影响的行数
     * @throws SQLException
     */
    public int modifySmsStatusByReqId(SmsInfo smsInfo) {
        int count = 0;
        try {
            String sql = "update t_sms_info set sms_status=?,sms_rsp_code=?,sms_rsp_msg=?,channel_no=?,req_id=? where id=?";
            count = dao.execute(sql, new Object[]{smsInfo.getSms_status(), smsInfo.getSms_rsp_code(), smsInfo.getSms_rsp_msg(), smsInfo.getChannel_no(), smsInfo.getReq_id(), smsInfo.getId()});
        } catch (Exception e) {
            log.error("手机号[{}]修改状态失败，系统出现未知异常：{}", smsInfo.getMobiles(), e);
            return 0;
        }
        return count;
    }

    /**
     * 更新短信状态
     *
     * @param smsInfo 短信实体对象
     * @return 受影响的行数
     * @throws SQLException
     */
    public int modifySmsStatusByReqIdAndStatus(SmsInfo smsInfo) {
        int count = 0;
        try {
            String sql = "update t_sms_info set sms_status=?,sms_rsp_code=?,sms_rsp_msg=? where req_id=? and (sms_status='2' or sms_status='6')";
            count = dao.execute(sql, new Object[]{smsInfo.getSms_status(), smsInfo.getSms_rsp_code(), smsInfo.getSms_rsp_msg(), smsInfo.getReq_id()});
        } catch (Exception e) {
            log.error("手机号[{}]修改短信发送状态出现异常：{}", smsInfo.getMobiles(), e);
            return count;
        }
        return count;
    }

    /**
     * 初始化短信信息
     *
     * @param smsInfo
     * @return
     * @throws SQLException
     */
    public long initSms(SmsInfo smsInfo) throws SQLException {
        List<Object> list = new ArrayList<Object>();
        list.add(smsInfo.getReq_id());
        list.add(smsInfo.getMobiles());
        list.add(smsInfo.getSms_content());
        list.add(smsInfo.getService_id());
        list.add(smsInfo.getSms_user_name());
        list.add(smsInfo.getSms_channel());
        list.add(smsInfo.getSms_status());
        list.add(smsInfo.getSms_rsp_code());
        list.add(smsInfo.getSms_rsp_msg());
        list.add(smsInfo.getSource_system());
        list.add(smsInfo.getSms_send_user());
        list.add(smsInfo.getChannel_no());
        list.add(smsInfo.getSms_type());
        list.add(smsInfo.getVali_code());
        list.add(smsInfo.getVali_status());
        list.add(smsInfo.getSms_business());
        String sql = "insert into t_sms_info(req_id,mobiles,sms_content,service_id,sms_user_name,sms_channel,sms_status,sms_rsp_code,"
                + "sms_rsp_msg,source_system,sms_send_user,channel_no,sms_type,vali_code,vali_status,sms_business) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        return dao.getSaveObjId(sql, list.toArray());
    }

    /**
     * 获取短信通道配置信息
     *
     * @param channelCode
     * @return
     */
    public Map<String, Object> getSmsChannelInfo(String channelCode) {
        String sql = "select * from t_sms_config where sms_platform_code=? and  platform_status=1";
        return dao.findFirst(sql, channelCode);
    }

    /**
     * 获取短信通道配置信息
     *
     * @return
     */
    public Map<String, Object> getSmsChannelInfo2() {
        String sql = "select * from t_sms_config where platform_status=1";
        return dao.findFirst(sql);
    }

    /**
     * 统计当天手机号发送短信成功次数
     *
     * @param moblie 手机号
     * @return 总条数
     */
    public long getSmsCount(String moblie) {
        long count = 0;
        String sql = "select count(id) count from t_sms_info where mobiles=? and sms_status in('2','4','6') and DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')";
        Map<String, Object> map = dao.findFirst(sql, moblie);
        if (map != null && !map.isEmpty()) {
            count = Integer.valueOf(map.get("count").toString());
        }
        return count;
    }

}
